<?php
namespace app\index\model;
use think\Model;
use think\Db;
class Myssql extends Model{
    public $conn;
    private $db;
    private  $host;
    private  $pass;
    public function __construct($host=null,$db=null,$pass=null)
    {
        if ($host && $db && $pass) {
            $this->host = $host;
            $this->db = $db;
            $this->pass = $pass;
            $dbConfig = [
                'type'            => 'sqlsrv',
                // // 服务器地址
                'hostname'        => $this->host,
                // // 数据库名
                'database'        => $this->db,
                // // 用户名
                'username'        => 'itreader',
                // // 密码Zxcv123!
                'password'        => $this->pass,
                // 连接dsn
                'dsn'             => 'sqlsrv:Server='.$this->host.';Database='.$this->db
            ];
            $this->conn = Db::connect($dbConfig);
        } 
    }


    //分页查询
    function queryWithPage($table, $filter="r1.*", $condition=null, $id, $type=null, $page, $limit, $order=null){
        $start = ($page - 1) * $limit;
        $end = $page * $limit;
        $count = $this->getCount($table, $condition);
        if($order){
            $sql = "select r2.n,$filter from $table as r1,(select top $end row_number() over(order by {$id} desc) as n ,$id from $table";
        }else{
            $sql = "select r2.n,$filter from $table as r1,(select top $end row_number() over(order by {$id}) as n ,$id from $table";
        }
        if($condition != null){
            $sql .= " where $condition";
        }
        $sql .= ") as r2 where r1.$id = r2.$id and r2.n > $start order by r2.n ";
        $res = $this->conn->query($sql);
        return array(
            'data'=>$res
            ,'count'=>$count
        );
    }

    //总数
    function getCount($table, $condition="1"){
        $sql = "select count(*) AS num from {$table} where {$condition}";
        $res = $this->conn->query($sql);
        $count=!empty($res)?$res[0]["num"]:0;
        return $count; 
    }

    function selectRaw($table, $filter="*", $condition=null){
        $sql = "select $filter from $table";
        if($condition != null){
            $sql .= " where $condition";
        }
        $res = $this->conn->query($sql);
        return $res;
    }

    function selectOne($table, $filter="*", $condition = null){
        $sql = "select top 1 $filter from $table  ";
        if($condition != null){
            $sql .= " where $condition";
        }
        return $this->conn->query($sql);
    }


}
?>